Рассылка "Магия таблиц"

Совсем скоро подписчикам придет восьмой выпуск рассылки! Там будут новости (в частности, в Office в целом и в Excel подвезли новый режим 🌓), несколько табличных лайфхаков и всякое жизненное (про путешествия и бег).

Подписаться можно тут:
✉️https://shagabutdinov.ru/#subscription

А пока — вот предыдущие выпуски:
Первый. Новости и немного про графической слой Excel и про срезы — один из типов объектов, живущих на нем.

Второй. про ссылки на умные таблицы в Google Spreadsheets, линейчатую диаграмму для визуализации план-факта или чего-то подобного и немного личного — про путешествие на край света🥝.

Третий. Макрос для создания Word’овских документов по шаблону и лайфхаки для навигации по листам Excel. А также книжные итоги года.

Четвертый. Пара новостей об изменениях в Excel, секретный секрет про очень скрытые листы и пара слов про поездку в Оман.

Пятый. про новую функцию УРЕЗДИАПАЗОН, старую функцию ИНДЕКС (но про ее применение, которое может стать новостью для многих из вас) и про парочку нетабличных статей

Шестой. пачка лайфхаков из новой (для меня и, думаю, для вас, но не для автора 😊) книги Билла Джелена, про запуск нового формата — обучение по подписке и немного стоицизма

Седьмой. Про новые видео по табличным формулам, диаграммно-гистограмные приемы и про крутую книгу о силе оптимизма 😊

B2:ИНДЕКС(...) — ссылка на диапазон динамических размеров

Функция ИНДЕКС / INDEX весьма разносторонне развита. Умеет она в числе прочего возвращать ссылку на ячейку вместо ее содержимого. Если поставить ее после двоеточия. Например, так:

A1:ИНДЕКС(...)

В примере первая ячейка диапазона для расчета среднего — это B2 (то есть январь в каждом столбце), а последняя возвращается ИНДЕКСом — исходя из числа в ячейке A16.

=СРЗНАЧ(B2:ИНДЕКС(B2:B12;$A$16))

Теперь можно менять число в ячейке A16 и получать обновленный результат.

Почему не СМЕЩ / OFFSET, которая тоже может возвращать диапазон переменного размера? Ее тоже можно использовать в таких ситуациях. Но учитывайте, что она волатильная, в отличие от ИНДЕКСа. То есть пересчитывается при любом изменении в книге, а не при изменении ячеек, которые на нее влияют.

Видеоурок: Функции для разделения текста: TEXTSPLIT и другие

Друзья, хочу поделиться с вами одним из уроков своего курса "Магия новых функций Excel" — про TEXTSPLIT и другие функции для разделения текстовых строк и извлечения фрагментов.

Полезного просмотра!

Весь курс можно найти здесь:
https://shagabutdinov.ru/magic-excel

Диаграммы в Excel: 11 приемов

— Быстрая вставка диаграммы
— 4 способа добавления новых данных
— Фильтрация данных на диаграмме в разных версиях Excel
— Изображения вместо столбиков
— Выделяем отдельные точки данных
— Выравниваем диаграммы
— Серая тема для черно-белой печати диаграмм
— Фоновое выделение периода на диаграмме
— И другие приемы!

Оффлайн-тренинг в Москве по Excel

Всем привет! Хочу провести двухдневный тренинг-интенсив по Excel в Москве в мае. Два полных дня, на выходных, с возможностью практиковаться, в небольшой группе (точно до 12 человек), чтобы была возможность пообщаться с каждым и все попрактиковать.

Напишите в лс или на [email protected], если есть пожелания по темам? А также любые вопросы/пожелания. Я хочу рассмотреть темы форматирования (и немного визуализации), настройки интерфейса, основы и не только по сводным таблицам и формулы. Такой прожиточный максимум.
Ну а следующими постами — анонимный опрос по темам и формату в целом)

Функция LET

В ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз, пригодится новая функция LET. Она есть в Excel 2021/2024/365 и Google Таблицах.

Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. И затем вычисление с использованием этих переменных.

Что она дает:
— легче воспринимать формулу (одно и то же выражение не повторяется много раз целиком)
— скорость
— экзотика: можно использовать, чтобы добавлять комментарии к формуле 😸

https://shagabutdinov.ru/tpost/47brblc2e1-novaya-funktsiya-excel-i-google-tablits

Видео. Извлекаем элементы из даты: месяц и год, номер недели, день недели номером и текстом, квартал

Друзья, привет! Вашему вниманию новое видео моего курса по подписке "Магия табличных формул".

В этом видео обсуждаем, как извлечь из даты месяц, год, квартал, номер недели и день недели, последний день месяца.
Заодно по ходу дела — несколько полезных горячих клавиш и ВПР с таблицей внутри формулы.

Это видео открыто и бесплатно для всех — смотрите по ссылке.

Подписаться можно по ссылке — каждую неделю минимум одно новое видео, доступны и все старые. С файлами-примерами.

Автоподбор ширины

По умолчанию, если ширины столбца не хватает, числа будут скукоживаться до экспоненциального формата, а потом и до решеток. Даты — сразу до решеток (еще решетки могут возникнуть в случае появления некорректной — отрицательной или слишком большой даты)

Можно сделать так, чтобы менялся размер шрифта, чтобы числа в любом случае отображались в ячейках даже при небольшой ширине столбца. Выделяем ячейки — Ctrl + 1 — вкладка "Выравнивание" — "автоподбор ширины" (Shrink to fit)

Смотрим на видео без звука!

Оффлайн-тренингу в Москве быть!
Тема: "Табличные формулы для продолжающих"
24-25 мая в Москве
https://shagabutdinov.ru/formulas-offline

UPD: остается 12 мест из 15

С учетом результатов опроса я решил посвятить оффлайн-тренинг формулам. В основном — формулам как для старых, так и для новых версий Excel (и Google Таблиц), но большой блок на полдня про новые функции и LAMBDA тоже будет.
Программа готова — но после общения с теми, кто запишется, я могу ее немного скорректировать и в целом нам ничто не помешает обсуждать в процессе дополнительные вопросы и нюансы, а не слепо следовать жестко утвержденной программе ;)
Я буду учитывать уровень и пожелания всех, потому что компьютерный класс небольшой — 15 человек максимум . Так что задам несколько вопросов тем, кто напишет, перед тем как забронировать место.

Кратко программа вот:
- Краткий ликбез по формулам: функции, ссылки, ошибки, формулы массива
- Логические формулы и функции. Проверка данных и условное форматирование с формулами
- Функции для работы с датами
- Функции для работы с текстом
- Расчеты с условиями (от СУММЕСЛИМН до элементов управления — флажки, кнопки — в сочетании с формулами)
- Поиск данных и объединение таблиц
- Новые функции Office 365 и Google Таблиц

А подробнее по ссылке.

Какой уровень нужен? Предполагается, что пальцы ваши неоднократно касались клавиши F4 и вы писали хотя бы минимально простые формулы (СУММировали что-то или даже отВПРили таблицу-другую).
Если есть сомнения — пишите мне, обсудим.

- До 10 апреля действует скидка 3 000₽
- Первые 10 участников получат почти килограмм таблиц в подарок (книгу "Магия таблиц" в твердом переплете). Можно будет и подписать (если вас устроит мой ужасный почерк и вы не планируете перепродать книгу на Авито потом, конечно 😸) Только десятерым не потому, что мне жалко, а потому что книги нет вообще. Я достал последние остатки из издательства и моих личных запасов. Новый тираж в целом будет, просто выйдет из типографии уже после обучения.

- В обучение включены кофе-брейки. Будут ноутбуки с 2021 офисом, но в идеале взять свой с Microsoft 365 и/или Google Таблицами, чтобы попрактиковаться с новыми функциями. Исходные, готовые файлы и сотня-другая слайдов с материалами по всем темам.

- Если вы решите отказаться от обучения, потому что вам не понравится, это можно сделать в первом перерыве первого дня с возвратом полной стоимости.

- Если вы хотите участвовать, напишите на [email protected]. Я пришлю ссылку на оплату онлайн. А также задам вам несколько вопросов про ваш уровень, планы и рабочие задачи, чтобы учесть это и сделать обучение максимально полезным для всех.
- Если ваше обучение оплачивает организация — оформим договор, счет и акт.

Старая и иногда добрая функция ПРОСМОТР / LOOKUP

Не ПРОСМОТРX / XLOOKUP, которая появилась в Excel 2021 и Google Таблицах. А функция без икса, которая есть во всех версиях. Для базовой задачи по поиску текстового значения в другой таблице подходит плохо, так как ее алгоритм поиска предполагает сортировку исходного диапазона по алфавиту, что сложно поддерживать (так что в старых версиях лучше ВПР / VLOOKUP с последним аргументом, равным нулю; а в новых и Google — ПРОСМОТРX)

Но зато с ПРОСМОТРом можно некоторые фокусы вытворять: например, находить последнее значение в столбце или вести нечеткий текстовый поиск (находить определенное слово в ячейке, а не точное соответствие)

Эти приемы и общая информация про функцию в статье:
https://shagabutdinov.ru/excel-lookup/

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

ОченьСкрытый Лист

Листы в Excel бывают видимыми (таким должен быть как минимум один лист в книге), скрытыми и очень скрытыми.

Просто скрыть можно в Excel (правая кнопка по ярлыку — скрыть)

А ОченьСкрыть — только в редакторе VBA.
Нажимаем Alt+F11, выбираем лист в Project Explorer, меняем свойство Visible на xlSheetVeryHidden

Теперь лист нельзя будет увидеть и показать в интерфейсе Excel. Но пользователь, знающий о таком свойстве, сможет его изменить в VBA. Еще такой лист будет показан, если пользователь запустит макрос, показывающий все скрытые листы.

И при подключении к файлу в Power Query будут видны все листы — и скрытые, и очень скрытые.
_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Найти и заменить: меняем форматы, а не значения

У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д.

И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.

Вызываем окно "Найти и заменить" — Ctrl + H

Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть

Нажимаем "Заменить все". Готово!

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Перемещаем столбец

Если выделить столбец и потянуть его вправо или влево за границу, зажав левую кнопку мыши, мы вырежем и вставим данные — то есть исходный столбец останется пустым, а тот, куда мы перетащили, заполнится его данными. Поэтому Excel сначала предупредит вас в диалоговом окне о том, что данные будут заменены (если они есть)

Ну а если надо переместить столбец, зажимаем клавишу Shift, тащим — и он просто перемещается. Уже без предупреждений :)

Тренинг "Табличные формулы для продолжающих" 45801 24 мая-25 мая

Вот в таком компьютерном классе пройдет интенсив по формулам ! Камерная обстановка и насыщенная интересная практика.

5 мест уже забронировано, так что осталось только 5 входных билетов с книгой "Магия таблиц" в подарок. Сегодня последний день, когда можно забронировать участие со скидкой 3 000 рублей.

Присоединяйтесь! Если есть любые вопросы и сомнения - пишите:
[email protected]

Оставить заявку можно по этому же адресу или на странице тренинга:
https://shagabutdinov.ru/formulas-offline

Там же программа.
В любом случае я немного поспрашиваю вас по поводу рабочих задач и вашего уровня перед бронированием места, чтобы понять, подойдет ли вам обучение.

У вас в ячейке ссылка. И вы эту ячейку хотите выделить.
Но при клике по ячейке происходит переход по ссылке! Открывается браузер. А-А-А-А!

Спокойно: просто кликаем, но не отпускаем левую кнопку мыши и удерживаем секунду-другую. Ячейка будет выделена без перехода по ссылке.

_ _ _
Курс "Магия новых функций Excel. Революция в табличных формулах" 🔥